using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Globalization;
using System.Linq;
using Nop.Core;
using Nop.Core.Caching;
using Nop.Core.Data;
using Nop.Core.Domain.Common;
using Nop.Core.Domain.Customers;
using Nop.Core.Domain.Orders;
using Nop.Core.Domain.Shipping;
using Nop.Core.Domain.Stores;
using Nop.Services.Common;
using Nop.Services.Events;
using Nop.Data;
using EntityFramework.Extensions;

namespace Nop.Services.Customers
{
    /// <summary>
    /// Customer service
    /// </summary>
    public partial class CustomerService : ICustomerService
    {
        #region Constants

        private const string CUSTOMER_CACHE_KEY = "Nop.customer-{0}";
        private const string CUSTOMERROLES_ALL_KEY = "Nop.customerrole.all-{0}";
        private const string CLUBMEMBERSHIP_ALL_KEY = "Nop.clubmembership.all";
        private const string CUSTOMERROLES_BY_ID_KEY = "Nop.customerrole.id-{0}";
        private const string CUSTOMERROLES_BY_SYSTEMNAME_KEY = "Nop.customerrole.systemname-{0}";
        private const string CUSTOMERROLES_PATTERN_KEY = "Nop.customerrole.";
        #endregion

        #region Fields

        private readonly IRepository<Customer> _customerRepository;
        private readonly IRepository<CustomerRole> _customerRoleRepository;
        private readonly IRepository<GenericAttribute> _gaRepository;
        private readonly IRepository<ClubMembership> _clubMembershipRepository;
        private readonly IRepository<Order> _orderRespository;
        private readonly IRepository<ClubMembershipHistory> _clubMembershipHistoryRepository;
        private readonly IGenericAttributeService _genericAttributeService;
        private readonly ICacheManager _cacheManager;
        private readonly IEventPublisher _eventPublisher;
        private readonly CustomerSettings _customerSettings;
        private readonly IDbContext _dbContext;

        #endregion

        #region Ctor

        /// <summary>
        /// Ctor
        /// </summary>
        /// <param name="cacheManager">Cache manager</param>
        /// <param name="customerRepository">Customer repository</param>
        /// <param name="customerRoleRepository">Customer role repository</param>
        /// <param name="gaRepository">Generic attribute repository</param>
        /// <param name="genericAttributeService">Generic attribute service</param>
        /// <param name="eventPublisher">Event published</param>
        public CustomerService(ICacheManager cacheManager,
            IRepository<Customer> customerRepository,
            IRepository<CustomerRole> customerRoleRepository,
            IRepository<GenericAttribute> gaRepository,
            IRepository<ClubMembership> clubMembershipRespository,
            IRepository<Order> orderRepository,
            IRepository<ClubMembershipHistory> membershipHistoryRespository,
            IGenericAttributeService genericAttributeService,
            IEventPublisher eventPublisher,
            CustomerSettings customerSettings,
            IDbContext dbContext)
        {
            this._cacheManager = cacheManager;
            this._customerRepository = customerRepository;
            this._customerRoleRepository = customerRoleRepository;
            this._gaRepository = gaRepository;
            this._clubMembershipRepository = clubMembershipRespository;
            this._orderRespository = orderRepository;
            this._clubMembershipHistoryRepository = membershipHistoryRespository;
            this._genericAttributeService = genericAttributeService;
            this._eventPublisher = eventPublisher;
            this._customerSettings = customerSettings;
            this._dbContext = dbContext;
        }

        #endregion

        #region Methods

        #region Customers

        /// <summary>
        /// Gets all customers
        /// </summary>
        /// <param name="registrationFrom">Customer registration from; null to load all customers</param>
        /// <param name="registrationTo">Customer registration to; null to load all customers</param>
        /// <param name="customerRoleIds">A list of customer role identifiers to filter by (at least one match); pass null or empty list in order to load all customers; </param>
        /// <param name="email">Email; null to load all customers</param>
        /// <param name="username">Username; null to load all customers</param>
        /// <param name="firstName">First name; null to load all customers</param>
        /// <param name="lastName">Last name; null to load all customers</param>
        /// <param name="dayOfBirth">Day of birth; 0 to load all customers</param>
        /// <param name="monthOfBirth">Month of birth; 0 to load all customers</param>
        /// <param name="company">Company; null to load all customers</param>
        /// <param name="phone">Phone; null to load all customers</param>
        /// <param name="cellPhone">CellPhone; null to load all customers</param>
        /// <param name="zipPostalCode">Phone; null to load all customers</param>
        /// <param name="loadOnlyWithShoppingCart">Value indicating whther to load customers only with shopping cart</param>
        /// <param name="sct">Value indicating what shopping cart type to filter; userd when 'loadOnlyWithShoppingCart' param is 'true'</param>
        /// <param name="pageIndex">Page index</param>
        /// <param name="pageSize">Page size</param>
        /// <returns>Customer collection</returns>
        public IPagedList<Customer> GetAllCustomers(DateTime? registrationFrom,
            DateTime? registrationTo, int[] customerRoleIds, bool roleSearchRestriction, string email, string username,
            string firstName, string lastName, int dayOfBirth, int monthOfBirth,
            string company, string phone, string cellPhone, string zipPostalCode,
            bool loadOnlyWithShoppingCart, ShoppingCartType? sct, int pageIndex, int pageSize
            )
        {
            var query = _customerRepository.Table;
            if (registrationFrom.HasValue)
                query = query.Where(c => registrationFrom.Value <= c.CreatedOnUtc);
            if (registrationTo.HasValue)
                query = query.Where(c => registrationTo.Value >= c.CreatedOnUtc);
            query = query.Where(c => !c.Deleted);
            if (customerRoleIds != null && customerRoleIds.Length > 0)
            {
                if (!roleSearchRestriction)
                {
                    // if roles match "any" in the customerRoleIds list.
                    //query = query.Where(c => c.CustomerRoleMappings.Select(cr => cr.Id).Intersect(customerRoleIds).Count() > 0);
                    query = query.Where(c => c.CustomerRoleMappings.Select(cr => cr.CustomerRoleId).Intersect(customerRoleIds).Any());
                }
                else
                {
                    // if roles must match "exactly" in the customerRoleIds list.
                    query = query.Where(c => c.CustomerRoleMappings.Select(cr => cr.CustomerRoleId).Count() == customerRoleIds.Count() &&
                                             c.CustomerRoleMappings.Select(cr => cr.CustomerRoleId).Intersect(customerRoleIds).Count() == customerRoleIds.Count());
                }
            }
            if (!String.IsNullOrWhiteSpace(email))
                query = query.Where(c => c.Email.Contains(email));
            if (!String.IsNullOrWhiteSpace(username))
                query = query.Where(c => c.Username.Contains(username));
            if (!String.IsNullOrWhiteSpace(firstName))
            {
                query = query
                    .Join(_gaRepository.Table, x => x.Id, y => y.EntityId, (x, y) => new { Customer = x, Attribute = y })
                    .Where((z => z.Attribute.KeyGroup == "Customer" &&
                        z.Attribute.Key == SystemCustomerAttributeNames.FirstName &&
                        z.Attribute.Value.Contains(firstName)))
                    .Select(z => z.Customer);
            }
            if (!String.IsNullOrWhiteSpace(lastName))
            {
                query = query
                    .Join(_gaRepository.Table, x => x.Id, y => y.EntityId, (x, y) => new { Customer = x, Attribute = y })
                    .Where((z => z.Attribute.KeyGroup == "Customer" &&
                        z.Attribute.Key == SystemCustomerAttributeNames.LastName &&
                        z.Attribute.Value.Contains(lastName)))
                    .Select(z => z.Customer);
            }
            //date of birth is stored as a string into database.
            //we also know that date of birth is stored in the following format YYYY-MM-DD (for example, 1983-02-18).
            //so let's search it as a string
            if (dayOfBirth > 0 && monthOfBirth > 0)
            {
                //both are specified
                string dateOfBirthStr = monthOfBirth.ToString("00", CultureInfo.InvariantCulture) + "-" + dayOfBirth.ToString("00", CultureInfo.InvariantCulture);
                //EndsWith is not supported by SQL Server Compact
                //so let's use the following workaround http://social.msdn.microsoft.com/Forums/is/sqlce/thread/0f810be1-2132-4c59-b9ae-8f7013c0cc00

                //we also cannot use Length function in SQL Server Compact (not supported in this context)
                //z.Attribute.Value.Length - dateOfBirthStr.Length = 5
                //dateOfBirthStr.Length = 5
                query = query
                    .Join(_gaRepository.Table, x => x.Id, y => y.EntityId, (x, y) => new { Customer = x, Attribute = y })
                    .Where((z => z.Attribute.KeyGroup == "Customer" &&
                        z.Attribute.Key == SystemCustomerAttributeNames.DateOfBirth &&
                        z.Attribute.Value.Substring(5, 5) == dateOfBirthStr))
                    .Select(z => z.Customer);
            }
            else if (dayOfBirth > 0)
            {
                //only day is specified
                string dateOfBirthStr = dayOfBirth.ToString("00", CultureInfo.InvariantCulture);
                //EndsWith is not supported by SQL Server Compact
                //so let's use the following workaround http://social.msdn.microsoft.com/Forums/is/sqlce/thread/0f810be1-2132-4c59-b9ae-8f7013c0cc00

                //we also cannot use Length function in SQL Server Compact (not supported in this context)
                //z.Attribute.Value.Length - dateOfBirthStr.Length = 8
                //dateOfBirthStr.Length = 2
                query = query
                    .Join(_gaRepository.Table, x => x.Id, y => y.EntityId, (x, y) => new { Customer = x, Attribute = y })
                    .Where((z => z.Attribute.KeyGroup == "Customer" &&
                        z.Attribute.Key == SystemCustomerAttributeNames.DateOfBirth &&
                        z.Attribute.Value.Substring(8, 2) == dateOfBirthStr))
                    .Select(z => z.Customer);
            }
            else if (monthOfBirth > 0)
            {
                //only month is specified
                string dateOfBirthStr = "-" + monthOfBirth.ToString("00", CultureInfo.InvariantCulture) + "-";
                query = query
                    .Join(_gaRepository.Table, x => x.Id, y => y.EntityId, (x, y) => new { Customer = x, Attribute = y })
                    .Where((z => z.Attribute.KeyGroup == "Customer" &&
                        z.Attribute.Key == SystemCustomerAttributeNames.DateOfBirth &&
                        z.Attribute.Value.Contains(dateOfBirthStr)))
                    .Select(z => z.Customer);
            }
            //search by company
            if (!String.IsNullOrWhiteSpace(company))
            {
                query = query
                    .Join(_gaRepository.Table, x => x.Id, y => y.EntityId, (x, y) => new { Customer = x, Attribute = y })
                    .Where((z => z.Attribute.KeyGroup == "Customer" &&
                        z.Attribute.Key == SystemCustomerAttributeNames.Company &&
                        z.Attribute.Value.Contains(company)))
                    .Select(z => z.Customer);
            }
            //search by phone
            if (!String.IsNullOrWhiteSpace(phone))
            {
                query = query
                    .Join(_gaRepository.Table, x => x.Id, y => y.EntityId, (x, y) => new { Customer = x, Attribute = y })
                    .Where((z => z.Attribute.KeyGroup == "Customer" &&
                        z.Attribute.Key == SystemCustomerAttributeNames.Phone &&
                        z.Attribute.Value.Contains(phone)))
                    .Select(z => z.Customer);
            }
            //search by cell phone
            if (!String.IsNullOrWhiteSpace(cellPhone))
            {
                query = query
                    .Join(_gaRepository.Table, x => x.Id, y => y.EntityId, (x, y) => new { Customer = x, Attribute = y })
                    .Where((z => z.Attribute.KeyGroup == "Customer" &&
                        z.Attribute.Key == SystemCustomerAttributeNames.CellPhone &&
                        z.Attribute.Value.Contains(cellPhone)))
                    .Select(z => z.Customer);
            }
            //search by zip
            if (!String.IsNullOrWhiteSpace(zipPostalCode))
            {
                query = query
                    .Join(_gaRepository.Table, x => x.Id, y => y.EntityId, (x, y) => new { Customer = x, Attribute = y })
                    .Where((z => z.Attribute.KeyGroup == "Customer" &&
                        z.Attribute.Key == SystemCustomerAttributeNames.ZipPostalCode &&
                        z.Attribute.Value.Contains(zipPostalCode)))
                    .Select(z => z.Customer);
            }

            if (loadOnlyWithShoppingCart)
            {
                int? sctId = null;
                if (sct.HasValue)
                    sctId = (int)sct.Value;

                query = sct.HasValue ?
                    query.Where(c => c.ShoppingCartItems.Where(x => x.ShoppingCartTypeId == sctId).Count() > 0) :
                    query.Where(c => c.ShoppingCartItems.Count() > 0);
            }

            query = query.OrderByDescending(c => c.CreatedOnUtc);

            var customers = new PagedList<Customer>(query, pageIndex, pageSize);
            return customers;
        }

        /// <summary>
        ///  Gets all customers
        /// </summary>
        /// <param name="dayOfBirth">birth day</param>
        /// <param name="monthOfBirth">birth month</param>
        /// <param name="customerRoleIds">role ids</param>
        /// <param name="pageIndex">page index</param>
        /// <param name="pageSize">page size</param>
        /// <returns>CustomerIncludeBirthday collection</returns>
        public IPagedList<CustomerIncludeBirthday> GetAllCustomer(int dayOfBirth, int monthOfBirth, int[] customerRoleIds, int pageIndex, int pageSize)
        {
            IPagedList<Customer> customers = GetAllCustomers(null, null, customerRoleIds, false, string.Empty, string.Empty, string.Empty, string.Empty, dayOfBirth, monthOfBirth, string.Empty, string.Empty, string.Empty, string.Empty, false, null, pageIndex, pageSize);

            if (customers == null || customers.Count <= 0) return new PagedList<CustomerIncludeBirthday>(new List<CustomerIncludeBirthday>(), pageIndex, pageSize);

            var customerAndBirthDic = new Dictionary<int, CustomerIncludeBirthday>();

            foreach (var item in customers.Where(item => !customerAndBirthDic.ContainsKey(item.Id)))
            {
                customerAndBirthDic.Add(item.Id, new CustomerIncludeBirthday()
                {
                    BaseCustomer = item
                });
            }

            if (dayOfBirth <= 0) return new PagedList<CustomerIncludeBirthday>(customerAndBirthDic.Values.ToList(), pageIndex, pageSize, customers.TotalCount);

            var customerBirthdays = _gaRepository.Table.Where(
                d =>
                    customerAndBirthDic.Keys.Contains(d.EntityId) && d.KeyGroup == "Customer" &&
                    d.Key == SystemCustomerAttributeNames.DateOfBirth).Select(d => new
                    {
                        Id = d.EntityId,
                        Value = d.Value
                    }).ToList();


            foreach (var item in customerBirthdays.Where(item => customerAndBirthDic.ContainsKey(item.Id)))
            {
                customerAndBirthDic[item.Id].DayOfBirtdhday = Convert.ToDateTime(item.Value);
            }


            return new PagedList<CustomerIncludeBirthday>(customerAndBirthDic.Values.ToList(), pageIndex, pageSize, customers.TotalCount);
        }

        /// <summary>
        /// Gets all customers with shopping carts
        /// </summary>
        /// <param name="sct">Value indicating what shopping cart type to filter; userd when 'loadOnlyWithShoppingCart' param is 'true'</param>
        /// <param name="pageIndex">Page index</param>
        /// <param name="pageSize">Page size</param>        
        /// <param name="storeId">The store ID, 1 for all stores</param>
        /// <returns>Customer collection</returns>
        public virtual IPagedList<Customer> GetAllCustomers(
            ShoppingCartType? sct,
            int pageIndex,
            int pageSize,
            int storeId
            )
        {
            var query = _customerRepository.Table;
            query = query.Where(c => !c.Deleted);

            int? sctId = null;
            if (sct.HasValue)
                sctId = (int)sct.Value;

            query = sct.HasValue ?
                query.Where(c => c.ShoppingCartItems.Where(x => x.ShoppingCartTypeId == sctId).Count() > 0) :
                query.Where(c => c.ShoppingCartItems.Count() > 0);

            if (storeId > 1)
            {
                query = query.Where(
                    c => c.ShoppingCartItems.Where(sci => sci.ProductVariant.Product.StoreId == storeId).Count() > 0);
            }

            query = query.OrderByDescending(c => c.CreatedOnUtc);

            var customers = new PagedList<Customer>(query, pageIndex, pageSize);
            return customers;
        }

        /// <summary>
        /// Gets all customers by affiliate identifier
        /// </summary>
        /// <param name="affiliateId">Affiliate identifier</param>
        /// <param name="pageIndex">Page index</param>
        /// <param name="pageSize">Page size</param>
        /// <returns>Customers</returns>
        public virtual IPagedList<Customer> GetAllCustomers(int affiliateId, int pageIndex, int pageSize)
        {
            var query = _customerRepository.Table;
            query = query.Where(c => !c.Deleted);
            query = query.Where(c => c.AffiliateId.HasValue && c.AffiliateId == affiliateId);
            query = query.OrderByDescending(c => c.CreatedOnUtc);

            var customers = new PagedList<Customer>(query, pageIndex, pageSize);
            return customers;
        }

        /// <summary>
        /// Gets all store managers by the store
        /// </summary>
        /// <param name="store">Store</param>
        /// <param name="includeAllStore">Include the managers can manager all stores</param>
        /// <returns>All managers can manage the store.</returns>
        public virtual IList<Customer> GetAllStoreManagers(Store store, bool includeAllStore = true)
        {
            if (store == null)
                throw new ArgumentNullException("store");

            var query = _customerRepository.Table;
            query = query.Where(c => !c.Deleted);

            if (includeAllStore)
                query = query.Where(c => c.StoreId == store.Id);
            else
                query = query.Where(c => c.StoreId == store.Id || c.StoreId == Store.AllStoreReservedId);

            query = query.Where(c => c.CustomerRoleMappings.Any(cr => cr.CustomerRoleId == 5));
            query = query.OrderByDescending(c => c.CreatedOnUtc);
            var customers = query.ToList();
            return customers;
        }

        /// <summary>
        /// Gets all customers by customer format (including deleted ones)
        /// </summary>
        /// <param name="passwordFormat">Password format</param>
        /// <returns>Customers</returns>
        public virtual IList<Customer> GetAllCustomersByPasswordFormat(PasswordFormat passwordFormat)
        {
            int passwordFormatId = (int)passwordFormat;

            var query = _customerRepository.Table;
            query = query.Where(c => c.PasswordFormatId == passwordFormatId);
            query = query.OrderByDescending(c => c.CreatedOnUtc);
            var customers = query.ToList();
            return customers;
        }

        /// <summary>
        /// Gets online customers
        /// </summary>
        /// <param name="lastActivityFromUtc">Customer last activity date (from)</param>
        /// <param name="customerRoleIds">A list of customer role identifiers to filter by (at least one match); pass null or empty list in order to load all customers; </param>
        /// <param name="pageIndex">Page index</param>
        /// <param name="pageSize">Page size</param>
        /// <returns>Customer collection</returns>
        public virtual IPagedList<Customer> GetOnlineCustomers(DateTime lastActivityFromUtc,
            int[] customerRoleIds, int pageIndex, int pageSize)
        {
            var query = _customerRepository.Table;
            query = query.Where(c => lastActivityFromUtc <= c.LastActivityDateUtc);
            query = query.Where(c => !c.Deleted);
            if (customerRoleIds != null && customerRoleIds.Length > 0)
                query = query.Where(c => c.CustomerRoleMappings.Select(cr => cr.CustomerRoleId).Intersect(customerRoleIds).Count() > 0);

            query = query.OrderByDescending(c => c.LastActivityDateUtc);
            var customers = new PagedList<Customer>(query, pageIndex, pageSize);
            return customers;
        }

        /// <summary>
        /// Gets all customers by customer role id
        /// </summary>
        /// <param name="customerRoleId">Customer role identifier</param>
        /// <param name="showHidden">A value indicating whether to show hidden records</param>
        /// <returns>Customer collection</returns>
        public virtual IList<Customer> GetCustomersByCustomerRoleId(int customerRoleId, bool showHidden = false)
        {
            var query = from c in _customerRepository.Table
                        from cr in c.CustomerRoleMappings
                        where (showHidden || c.Active) &&
                            !c.Deleted &&
                            cr.CustomerRoleId == customerRoleId
                            && ((cr.StartDate == null || cr.StartDate.Value < DateTime.UtcNow)
                               && (cr.EndDate == null || cr.EndDate.Value > DateTime.UtcNow))
                        orderby c.CreatedOnUtc descending
                        select c;

            var customers = query.ToList();
            return customers;
        }

        /// <summary>
        /// Delete a customer
        /// </summary>
        /// <param name="customer">Customer</param>
        public virtual void DeleteCustomer(Customer customer)
        {
            if (customer == null)
                throw new ArgumentNullException("customer");

            if (customer.IsSystemAccount)
                throw new NopException(string.Format("System customer account ({0}) could not be deleted", customer.SystemName));

            customer.Deleted = true;

            if (_customerSettings.SuffixDeletedCustomers)
            {
                if (!String.IsNullOrEmpty(customer.Email))
                    customer.Email += "-DELETED";
                if (!String.IsNullOrEmpty(customer.Username))
                    customer.Username += "-DELETED";
            }

            UpdateCustomer(customer);
        }

        /// <summary>
        /// Gets a customer
        /// </summary>
        /// <param name="customerId">Customer identifier</param>
        /// <returns>A customer</returns>
        public virtual Customer GetCustomerById(int customerId)
        {
            if (customerId == 0)
                return null;
            var customer = this._cacheManager.Get<Customer>(string.Format(CUSTOMER_CACHE_KEY, customerId), () =>
            {
                var cust = _customerRepository.GetById(customerId);
                return cust;
            });

            return customer;
        }

        /// <summary>
        /// Get customers by identifiers
        /// </summary>
        /// <param name="customerIds">Customer identifiers</param>
        /// <returns>Customers</returns>
        public virtual IList<Customer> GetCustomersByIds(int[] customerIds)
        {
            if (customerIds == null || customerIds.Length == 0)
                return new List<Customer>();

            var query = from c in _customerRepository.Table
                        where customerIds.Contains(c.Id)
                        select c;
            var customers = query.ToList();
            //sort by passed identifiers
            var sortedCustomers = new List<Customer>();
            foreach (int id in customerIds)
            {
                var customer = customers.Find(x => x.Id == id);
                if (customer != null)
                    sortedCustomers.Add(customer);
            }
            return sortedCustomers;
        }

        /// <summary>
        /// Gets a customer by GUID
        /// </summary>
        /// <param name="customerGuid">Customer GUID</param>
        /// <returns>A customer</returns>
        public virtual Customer GetCustomerByGuid(Guid customerGuid)
        {
            if (customerGuid == Guid.Empty)
                return null;

            var customer = this._cacheManager.Get<Customer>(string.Format(CUSTOMER_CACHE_KEY, customerGuid), () =>
            {
                var query = from c in _customerRepository.Table
                            where c.CustomerGuid == customerGuid
                            orderby c.Id
                            select c;
                var cust = query.FirstOrDefault();
                return cust;
            });

            return customer;
        }

        /// <summary>
        /// Get customer by email
        /// </summary>
        /// <param name="email">Email</param>
        /// <returns>Customer</returns>
        public virtual Customer GetCustomerByEmail(string email)
        {
            if (string.IsNullOrWhiteSpace(email))
                return null;

            var customer = this._cacheManager.Get<Customer>(string.Format(CUSTOMER_CACHE_KEY, email), () =>
            {

                var query = from c in _customerRepository.Table
                            orderby c.Id
                            where c.Email == email
                            select c;
                var cust = query.FirstOrDefault();
                return cust;
            });

            return customer;
        }



        /// <summary>
        /// Get customer by system name
        /// </summary>
        /// <param name="systemName">System name</param>
        /// <returns>Customer</returns>
        public virtual Customer GetCustomerBySystemName(string systemName)
        {
            if (string.IsNullOrWhiteSpace(systemName))
                return null;

            var customer = this._cacheManager.Get<Customer>(string.Format(CUSTOMER_CACHE_KEY, systemName), () =>
            {
                var query = from c in _customerRepository.Table
                            orderby c.Id
                            where c.SystemName == systemName
                            select c;
                var cust = query.FirstOrDefault();
                return cust;
            });
            return customer;
        }

        /// <summary>
        /// Get customer by username
        /// </summary>
        /// <param name="username">Username</param>
        /// <returns>Customer</returns>
        public virtual Customer GetCustomerByUsername(string username)
        {
            if (string.IsNullOrWhiteSpace(username))
                return null;

            var customer = this._cacheManager.Get<Customer>(string.Format(CUSTOMER_CACHE_KEY, username), () =>
            {
                var query = from c in _customerRepository.Table
                            orderby c.Id
                            where c.Username == username
                            select c;
                var cust = query.FirstOrDefault();
                return cust;
            });
            return customer;
        }

        /// <summary>
        /// Get customers by language identifier
        /// </summary>
        /// <param name="languageId">Language identifier</param>
        /// <returns>Customers</returns>
        public virtual IList<Customer> GetCustomersByLanguageId(int languageId)
        {
            var query = _customerRepository.Table;
            if (languageId > 0)
                query = query.Where(c => c.LanguageId.HasValue && c.LanguageId.Value == languageId);
            else
                query = query.Where(c => !c.LanguageId.HasValue);
            query = query.OrderBy(c => c.Id);
            var customers = query.ToList();
            return customers;
        }

        /// <summary>
        /// Get customers by currency identifier
        /// </summary>
        /// <param name="currencyId">Currency identifier</param>
        /// <returns>Customers</returns>
        public virtual IList<Customer> GetCustomersByCurrencyId(int currencyId)
        {
            var query = _customerRepository.Table;
            if (currencyId > 0)
                query = query.Where(c => c.CurrencyId.HasValue && c.CurrencyId.Value == currencyId);
            else
                query = query.Where(c => !c.CurrencyId.HasValue);
            query = query.OrderBy(c => c.Id);
            var customers = query.ToList();
            return customers;
        }

        /// <summary>
        /// Insert a guest customer
        /// </summary>
        /// <returns>Customer</returns>
        public virtual Customer InsertGuestCustomer()
        {
            var customer = new Customer()
            {
                CustomerGuid = Guid.NewGuid(),
                Active = true,
                CreatedOnUtc = DateTime.UtcNow,
                LastActivityDateUtc = DateTime.UtcNow,
            };

            //add to 'Guests' role
            var guestRole = GetCustomerRoleBySystemName(SystemCustomerRoleNames.Guests);
            if (guestRole == null)
                throw new NopException("'Guests' role could not be loaded");
            customer.CustomerRoleMappings.Add(new CustomerRoleMapping() { CustomerId = customer.Id, CustomerRoleId = guestRole.Id, StartDate = DateTime.UtcNow });

            _customerRepository.Insert(customer);

            return customer;
        }

        /// <summary>
        /// Insert a customer
        /// </summary>
        /// <param name="customer">Customer</param>
        public virtual void InsertCustomer(Customer customer)
        {
            if (customer == null)
                throw new ArgumentNullException("customer");

            _customerRepository.Insert(customer);

            //event notification
            _eventPublisher.EntityInserted(customer);
        }

        /// <summary>
        /// Updates the customer
        /// </summary>
        /// <param name="customer">Customer</param>
        public virtual void UpdateCustomer(Customer customer)
        {
            if (customer == null)
                throw new ArgumentNullException("customer");

            _customerRepository.Update(customer);

            RemoveCustomerCache(customer);

            //event notification
            _eventPublisher.EntityUpdated(customer);
        }

        private void RemoveCustomerCache(Customer customer)
        {
            var cacheKey = string.Format(CUSTOMER_CACHE_KEY, customer.Id);
            _cacheManager.Remove(cacheKey);
			cacheKey = string.Format(CUSTOMER_CACHE_KEY, customer.CustomerGuid);
			_cacheManager.Remove(cacheKey);
            cacheKey = string.Format(CUSTOMER_CACHE_KEY, customer.Email);
            _cacheManager.Remove(cacheKey);
            cacheKey = string.Format(CUSTOMER_CACHE_KEY, customer.Username);
            _cacheManager.Remove(cacheKey);
        }

        /// <summary>
        /// Reset data required for checkout
        /// </summary>
        /// <param name="customer">Customer</param>
        /// <param name="clearCouponCodes">A value indicating whether to clear coupon code</param>
        /// <param name="clearCheckoutAttributes">A value indicating whether to clear selected checkout attributes</param>
        /// <param name="clearRewardPoints">A value indicating whether to clear "Use reward points" flag</param>
        /// <param name="clearShippingMethod">A value indicating whether to clear selected shipping method</param>
        /// <param name="clearPaymentMethod">A value indicating whether to clear selected payment method</param>
        public virtual void ResetCheckoutData(Customer customer,
            bool clearCouponCodes = false, bool clearCheckoutAttributes = false,
            bool clearRewardPoints = true, bool clearShippingMethod = true,
            bool clearPaymentMethod = true)
        {
            if (customer == null)
                throw new ArgumentNullException();

            //clear entered coupon codes
            if (clearCouponCodes)
            {
                customer.DiscountCouponCode = "";
                customer.GiftCardCouponCodes = "";
            }

            //clear checkout attributes
            if (clearCheckoutAttributes)
            {
                customer.CheckoutAttributes = "";
            }

            //clear reward points flag
            if (clearRewardPoints)
            {
                customer.UseRewardPointsDuringCheckout = false;
            }

            //clear selected shipping method
            if (clearShippingMethod)
            {
                _genericAttributeService.SaveAttribute<ShippingOption>(customer, SystemCustomerAttributeNames.LastShippingOption, null);
                _genericAttributeService.SaveAttribute<ShippingOption>(customer, SystemCustomerAttributeNames.OfferedShippingOptions, null);
            }

            //clear selected payment method
            if (clearPaymentMethod)
            {
                customer.SelectedPaymentMethodSystemName = "";
            }

            UpdateCustomer(customer);
        }

        /// <summary>
        /// Delete guest customer records
        /// </summary>
        /// <param name="registrationFrom">Customer registration from; null to load all customers</param>
        /// <param name="registrationTo">Customer registration to; null to load all customers</param>
        /// <param name="onlyWithoutShoppingCart">A value indicating whether to delete customers only without shopping cart</param>
        /// <returns>Number of deleted customers</returns>
        public virtual int DeleteGuestCustomers(DateTime? registrationFrom,
            DateTime? registrationTo, bool onlyWithoutShoppingCart)
        {
            var guestRole = GetCustomerRoleBySystemName(SystemCustomerRoleNames.Guests);
            if (guestRole == null)
                throw new NopException("'Guests' role could not be loaded");

            var query = _customerRepository.Table;
            if (registrationFrom.HasValue)
                query = query.Where(c => registrationFrom.Value <= c.CreatedOnUtc);
            if (registrationTo.HasValue)
                query = query.Where(c => registrationTo.Value >= c.CreatedOnUtc);
            query = query.Where(c => c.CustomerRoleMappings.Select(cr => cr.CustomerRoleId).Contains(guestRole.Id));
            if (onlyWithoutShoppingCart)
                query = query.Where(c => c.ShoppingCartItems.Count() == 0);
            //no orders
            query = query.Where(c => c.Orders.Count() == 0);
            //no customer content
            query = query.Where(c => c.CustomerContent.Count() == 0);
            //ensure that customers doesn't have forum posts or topics
            query = query.Where(c => c.ForumTopics.Count() == 0);
            query = query.Where(c => c.ForumPosts.Count() == 0);
            //don't delete system accounts
            query = query.Where(c => !c.IsSystemAccount);

            int numberOfDeletedCustomers = 0;

            try
            {
                //delete from database
                numberOfDeletedCustomers = query.Delete();
            }
            catch (Exception exc)
            {
                Debug.WriteLine(exc);
            }

            return numberOfDeletedCustomers;
        }

        #endregion

        #region Customer roles

        /// <summary>
        /// Delete a customer role
        /// </summary>
        /// <param name="customerRole">Customer role</param>
        public virtual void DeleteCustomerRole(CustomerRole customerRole)
        {
            if (customerRole == null)
                throw new ArgumentNullException("customerRole");

            if (customerRole.IsSystemRole)
                throw new NopException("System role could not be deleted");

            _customerRoleRepository.Delete(customerRole);

            _cacheManager.RemoveByPattern(CUSTOMERROLES_PATTERN_KEY);

            //event notification
            _eventPublisher.EntityDeleted(customerRole);
        }

        /// <summary>
        /// Gets a customer role
        /// </summary>
        /// <param name="customerRoleId">Customer role identifier</param>
        /// <returns>Customer role</returns>
        public virtual CustomerRole GetCustomerRoleById(int customerRoleId)
        {
            if (customerRoleId == 0)
                return null;

            string key = string.Format(CUSTOMERROLES_BY_ID_KEY, customerRoleId);
            return _cacheManager.Get(key, () =>
            {
                var customerRole = _customerRoleRepository.GetById(customerRoleId);
                return customerRole;
            });
        }

        /// <summary>
        /// Gets a customer role
        /// </summary>
        /// <param name="systemName">Customer role system name</param>
        /// <returns>Customer role</returns>
        public virtual CustomerRole GetCustomerRoleBySystemName(string systemName)
        {
            if (String.IsNullOrWhiteSpace(systemName))
                return null;

            string key = string.Format(CUSTOMERROLES_BY_SYSTEMNAME_KEY, systemName);
            return _cacheManager.Get(key, () =>
            {
                var query = from cr in _customerRoleRepository.Table
                            orderby cr.Id
                            where cr.SystemName == systemName
                            select cr;
                var customerRole = query.FirstOrDefault();
                return customerRole;
            });
        }

        /// <summary>
        /// Gets all customer roles
        /// </summary>
        /// <param name="showHidden">A value indicating whether to show hidden records</param>
        /// <returns>Customer role collection</returns>
        public virtual IList<CustomerRole> GetAllCustomerRoles(bool showHidden = false)
        {
            string key = string.Format(CUSTOMERROLES_ALL_KEY, showHidden);
            return _cacheManager.Get(key, () =>
            {
                var query = from cr in _customerRoleRepository.Table
                            orderby cr.Name
                            where (showHidden || cr.Active)
                            select cr;
                var customerRoles = query.ToList();
                return customerRoles;
            });
        }

        /// <summary>
        /// Inserts a customer role
        /// </summary>
        /// <param name="customerRole">Customer role</param>
        public virtual void InsertCustomerRole(CustomerRole customerRole)
        {
            if (customerRole == null)
                throw new ArgumentNullException("customerRole");

            _customerRoleRepository.Insert(customerRole);

            _cacheManager.RemoveByPattern(CUSTOMERROLES_PATTERN_KEY);

            //event notification
            _eventPublisher.EntityInserted(customerRole);
        }

        /// <summary>
        /// Updates the customer role
        /// </summary>
        /// <param name="customerRole">Customer role</param>
        public virtual void UpdateCustomerRole(CustomerRole customerRole)
        {
            if (customerRole == null)
                throw new ArgumentNullException("customerRole");

            _customerRoleRepository.Update(customerRole);

            _cacheManager.RemoveByPattern(CUSTOMERROLES_PATTERN_KEY);

            //event notification
            _eventPublisher.EntityUpdated(customerRole);
        }

        #endregion

        #region Customer district

        /// <summary>
        /// Sets the district identifier associated with the current permission.
        /// </summary>
        /// <param name="customer">Customer</param>
        /// <param name="districtId">District identifier</param>
        public virtual void SetDistrictId(Customer customer, int? districtId)
        {
            _genericAttributeService.SaveAttribute<int?>(customer, SystemCustomerAttributeNames.AuthorizedDistrict, districtId);
        }

        /// <summary>
        /// Gets the district identifier associated with the current permission.
        /// </summary>
        /// <param name="customer">Customer</param>
        /// <returns>District identifier</returns>
        public virtual int? GetDistrictId(Customer customer)
        {
            return customer.GetAttribute<int?>(SystemCustomerAttributeNames.AuthorizedDistrict);
        }

        public virtual IList<ClubMembership> GetAllClubMemberships()
        {
            string key = string.Format(CLUBMEMBERSHIP_ALL_KEY);
            return _cacheManager.Get(key, () =>
            {
                var query = from cm in _clubMembershipRepository.Table
                            select cm;
                var memberships = query.ToList();
                return memberships;
            });
        }

        public virtual void InsertMembershipHistory(ClubMembershipHistory membershipHistory)
        {
            _clubMembershipHistoryRepository.Insert(membershipHistory);
            _eventPublisher.EntityInserted(membershipHistory);
        }

        public virtual IList<ClubMembershipHistory> GetMembershipHistory(int customerId)
        {
            return _clubMembershipHistoryRepository.Table.Where(h => h.CustomerId == customerId).ToList();
        }
        #endregion


        #region ExecuteSqlCommand
        public virtual bool ExcuteSql(string sql, Customer customer=null)
        {
            try
            {
                var iSql = _dbContext.ExecuteSqlCommand(sql);
                if (iSql > 0)
                {
                    if (customer != null)
                    {
                        RemoveCustomerCache(customer);
                    }
                    return true;
                }
                return false;
            }
            catch
            {
                return false;
            }
        }
        #endregion

        #endregion
    }
}